﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using CepFacil.API;
using JMI_Nucleo.Enumerador;
using MySql.Data.MySqlClient;
using JMI_Nucleo.Classe;
using System.IO;

namespace JMI_Nucleo.View.Form_Util
{
    public partial class FrmBuscaCep : Form
    {
        private const string tabelaLocal = "cep";
        private string tabelaCep = "sp";
        private MySqlConnection conn = null;
        private MySqlDataAdapter adapter = null;
        private MySqlCommand comando = null;
        private MySqlDataReader rd = null;
        private int pagina = 0;
        private DataTable tb = new DataTable();
        private DataSet dataset = null;
        private DataRow rwRegistro = null;
        private string aux = String.Empty;
        private mCep _mCep = new mCep();

        public mCep InfoCep { get { return _mCep; } set { _mCep = value; } }

        public FrmBuscaCep()
        {
            InitializeComponent();
        }

        private string GetStringConexao()
        {
            string arquivoINI = Path.Combine(Directory.GetCurrentDirectory(), "configcep.ini");
            IniFile iniFile = new IniFile(arquivoINI);
            string servidor = iniFile.IniReadValue("PARAMETRO", "Servidor");
            int porta = Convert.ToInt32(iniFile.IniReadValue("PARAMETRO", "Porta"));
            string _base = iniFile.IniReadValue("PARAMETRO", "Base");
            string usuario = iniFile.IniReadValue("PARAMETRO", "Usuario");
            string senha = iniFile.IniReadValue("PARAMETRO", "Senha");
           
            string strConexao = String.Format("Server={0};Port={1};Database={2};User Id={3};Password={4};",
                     servidor, porta, _base, usuario, senha);

            return strConexao;
        }

        public List<mCep> Listar(int pagina, string Municipio = "")
        {
            string cmdSQL = String.Empty;
            conn = new MySqlConnection(GetStringConexao());
            conn.Open();

            adapter = new MySqlDataAdapter();
            comando = new MySqlCommand();
            dataset = new DataSet();

            try
            {
                dataset.Tables.Add(new DataTable(tabelaLocal));

                comando.CommandTimeout = 0;
                comando.Connection = conn;
                if (Municipio != String.Empty)
                    cmdSQL = @"SELECT * FROM " + tabelaCep + " WHERE cidade = '" + Municipio + "'";
                else
                    cmdSQL = @"SELECT * FROM " + tabelaCep + " ORDER BY cidade";

                comando.CommandText = cmdSQL;

                adapter.SelectCommand = comando;

                if (pagina > -1)
                    adapter.Fill(dataset, pagina * 25, 25, tabelaLocal);
                else
                    adapter.Fill(dataset, tabelaLocal);

                List<mCep> cache = new List<mCep>();
                
                this.Cursor = Cursors.WaitCursor;
                foreach (DataRow row in dataset.Tables[tabelaLocal].Rows)
                {
                    _mCep.Bairro = "Bairro: " + row["bairro"].ToString();
                    _mCep.Cep = row["cep"].ToString();
                    _mCep.Cidade = row["cidade"].ToString();
                    _mCep.TipoLogradouro = row["tp_logradouro"].ToString();
                    _mCep.Logradouro = row["logradouro"].ToString();
                    _mCep.UF = tabelaCep.ToUpper();
                    if (_mCep.GetDicUF.ContainsKey(_mCep.UF))
                       _mCep.DescricaoUF = _mCep.GetDicUF[_mCep.UF];
                    cache.Add(_mCep);
                    _mCep = new mCep();
                }
                this.Cursor = Cursors.Default;
                return cache;
            }
            finally
            {
                if (conn.State != ConnectionState.Closed) { conn.Close(); }
                dataset.Dispose();
                adapter.Dispose();
                comando.Dispose();
                conn.Dispose();
            }
        }

        private void CarregaMunicipioETipoLogradouro()
        {
            conn = new MySqlConnection(GetStringConexao());
            conn.Open();

            comando = new MySqlCommand();
        
            try
            {
                this.Cursor = Cursors.WaitCursor;
                // Carrega cidade
                comando.CommandTimeout = 0;
                comando.Connection = conn;
                comando.CommandText = @"SELECT distinct cidade FROM " + tabelaCep + " ORDER BY cidade";
                comando.Prepare();
                rd = comando.ExecuteReader();
                
                cboMunicipio.Items.Clear();
                while (rd.Read())
                {
                    cboMunicipio.Items.Add(rd["cidade"].ToString());
                }
                rd.Close();
                // Carrga tipo de logradouro
                comando.CommandTimeout = 0;
                comando.Connection = conn;
                comando.CommandText = @"SELECT distinct tp_logradouro FROM " + tabelaCep + " ORDER BY tp_logradouro";
                comando.Prepare();
                rd = comando.ExecuteReader();

                cboTipoLogradouro.Items.Clear();
                while (rd.Read())
                {
                    cboTipoLogradouro.Items.Add(rd["tp_logradouro"].ToString());
                }
                rd.Close();
                this.Cursor = Cursors.Default;
            }
            finally
            {
                if (conn.State != ConnectionState.Closed) { conn.Close(); }
                comando.Dispose();
                conn.Dispose();
            }
        }

        private void CarregaListaUF()
        {
            cboUF.Items.Clear();
            foreach (string strChave in _mCep.GetDicUF.Keys)
            {
                cboUF.Items.Add(strChave);
            }
        }

        private void CarregarLista(string Municipio = "")
        {
            bsCep.SuspendBinding();
            try
            {
                bsCep.DataSource = Listar(this.pagina, Municipio);
                Application.DoEvents();
                if (bsCep.List.Count == 0)
                {
                    BtnProximo.Enabled = false;
                    this.pagina--;
                    this.CarregarLista(Municipio);
                }
                else
                    BtnProximo.Enabled = (bsCep.List.Count >= 1);

                BtnAnterior.Enabled = (this.pagina > 0);
                Application.DoEvents();
            }
            finally
            {
                bsCep.ResumeBinding();
                dgwCep.Select();
            }
        }

        public mCep GetRegistro(string Campo, string Filtro)
        {
            conn = new MySqlConnection(GetStringConexao());
            conn.Open();

            adapter = new MySqlDataAdapter();
            comando = new MySqlCommand();
            dataset = new DataSet();

            try
            {
                dataset.Tables.Add(new DataTable(tabelaLocal));

                comando.CommandTimeout = 0;
                comando.Connection = conn;
                comando.CommandText = @"SELECT * FROM " + tabelaCep + " WHERE " + Campo + Filtro;

                adapter.SelectCommand = comando;

                adapter.Fill(dataset, tabelaLocal);

                _mCep = new mCep();

                if (dataset.Tables[tabelaLocal].Rows.Count > 0)
                {
                    rwRegistro = dataset.Tables[tabelaLocal].Rows[0];

                    _mCep.Bairro = "Bairro: " + rwRegistro["bairro"].ToString();
                    _mCep.Cep = rwRegistro["cep"].ToString();
                    _mCep.Cidade = rwRegistro["cidade"].ToString();
                    _mCep.TipoLogradouro = rwRegistro["tp_logradouro"].ToString();
                    _mCep.Logradouro = rwRegistro["logradouro"].ToString();
                    _mCep.UF = tabelaCep.ToUpper();
                    if (_mCep.GetDicUF.ContainsKey(_mCep.UF))
                        _mCep.DescricaoUF = _mCep.GetDicUF[_mCep.UF];
                }
                return _mCep;
            }
            finally
            {
                if (conn.State != ConnectionState.Closed) { conn.Close(); }
                dataset.Dispose();
                adapter.Dispose();
                comando.Dispose();
                conn.Dispose();
            }
        }

        private void TxtPesquisa_TextChanged(object sender, EventArgs e)
        {
            if (TxtPesquisa.Text.Length == 9)
            {
                tb = dataset.Tables[0];
                for (int x = 0; x < tb.Rows.Count; x++)
                {
                    rwRegistro = tb.Rows[x];
                    aux = rwRegistro["cep"].ToString();
                    if (TxtPesquisa.Text == aux)
                    {
                        bsCep.DataSource = GetRegistro("cep", " = '" + TxtPesquisa.Text + "'");
                        break;
                    }
                }
            }
            else if (TxtPesquisa.Text.Length > 9)
            {
                tb = dataset.Tables[0];
                
                for (int x = 0; x < tb.Rows.Count; x++)
                {
                    rwRegistro = tb.Rows[x];
                    aux = rwRegistro["logradouro"].ToString();
                    _mCep = GetRegistro("logradouro", " LIKE '" + TxtPesquisa.Text + "%'");
                    if (_mCep != null)
                    {
                        bsCep.DataSource = _mCep;
                        break;
                    }
                }
            }
            else if (TxtPesquisa.Text == String.Empty)
                bsCep.DataSource = tb;
            dgwCep.Refresh();
        }

        private void BtnLocalizar_Click(object sender, EventArgs e)
        {
            Localidade localidade = CepFacilBusca.BuscarLocalidade(TxtPesquisa.Text, Constante.CODIGO_CEP_FACIL);

            rchRetorno.Clear();
            rchRetorno.AppendText("Tipo de logradouro: " + localidade.Tipo);
            rchRetorno.AppendText("Logradouro: " + localidade.Logradouro);
            rchRetorno.AppendText("Bairro: " + localidade.Bairro);
            rchRetorno.AppendText("Cidade: " + localidade.Cidade);
            rchRetorno.AppendText("UF: " + localidade.Uf);

            rchRetorno.AppendText("CEP: " + localidade.Cep);
        }

        private void BtnCancelar_Click(object sender, EventArgs e)
        {
            Close();
        }

        private void BtnOK_Click(object sender, EventArgs e)
        {
            if (dgwCep.SelectedRows.Count > 0)
            {
                int selecionado = dgwCep.CurrentRow.Index;
                string s = dgwCep.Rows[selecionado].Cells["colCep"].Value.ToString();
                InfoCep = GetRegistro("cep", " = '" + s + "'");
                BtnCancelar_Click(sender, new EventArgs());
            }
        }

        private void FrmBuscaCep_Load(object sender, EventArgs e)
        {
            rbOffLine.Checked = false;
            rbOnline.Checked = true;
            rbOnline_Click(sender, new EventArgs());
        }

        private void rbOnline_Click(object sender, EventArgs e)
        {
            lblTitulo.Text = "Busca de CEPs Online";
            gbFiltro.Enabled = false;
            label2.Enabled = false;
            label3.Enabled = false;
            label4.Enabled = false;
            cboUF.Enabled = false;
            cboTipoLogradouro.Enabled = false;
            cboMunicipio.Enabled = false;
            pnlOffLine.Visible = false;
            pnlOffLine.Dock = DockStyle.None;
            rchRetorno.Visible = true;
            rchRetorno.Dock = DockStyle.Fill;
            BtnAnterior.Enabled = false;
            BtnProximo.Enabled = false;
        }

        private void rbOffLine_Click(object sender, EventArgs e)
        {
            lblTitulo.Text = "Busca de CEPs Offline";
            gbFiltro.Enabled = true;
            label2.Enabled = true;
            label3.Enabled = true;
            label4.Enabled = true;
            cboUF.Enabled = true;
            cboTipoLogradouro.Enabled = true;
            cboMunicipio.Enabled = true;
            rchRetorno.Dock = DockStyle.None;
            rchRetorno.Visible = false;
            pnlOffLine.Visible = true;
            pnlOffLine.Dock = DockStyle.Fill;
            BtnAnterior.Enabled = true;
            BtnProximo.Enabled = true;
            pnlInfo.Visible = true;
            pnlInfo.Show();
            CarregarLista();
            CarregaListaUF();
            CarregaMunicipioETipoLogradouro();
            pnlInfo.Visible = false;
        }

        private void BtnProximo_Click(object sender, EventArgs e)
        {
            this.Cursor = Cursors.AppStarting;
            try
            {
                this.pagina++;
                CarregarLista();
            }
            finally
            {
                this.Cursor = Cursors.Default;
            }
        }

        private void BtnAnterior_Click(object sender, EventArgs e)
        {
            this.Cursor = Cursors.AppStarting;
            try
            {
                if (pagina > 0) this.pagina--;
                CarregarLista();
            }
            finally
            {
                this.Cursor = Cursors.Default;
            }
        }
                
        private void cboMunicipio_SelectedValueChanged(object sender, EventArgs e)
        {
            if (cboMunicipio.Text != String.Empty)
                CarregarLista(cboMunicipio.Text);
        }

        private void cboUF_SelectedValueChanged(object sender, EventArgs e)
        {
            if (!String.IsNullOrEmpty(cboUF.Text) && cboUF.Text.Length == 2)
            {
                lblDescricaoUF.Text = _mCep.GetDicUF[cboUF.Text.ToUpper()];
                tabelaCep = cboUF.Text;
                CarregarLista();
                CarregaMunicipioETipoLogradouro();
            }
        }

        private void FrmBuscaCep_KeyUp(object sender, KeyEventArgs e)
        {
            switch (e.KeyCode)
            {
                case Keys.F3:
                    BtnOK_Click(sender, new EventArgs());
                    break;
                case Keys.Escape:
                    BtnCancelar_Click(sender, new EventArgs());
                    break;
            }
        }
    }        
}
